﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SQLite;
using System.Data;

namespace FMWeb.CSCode
{
    public static class DBManager
    {
        public static string gameDBFileName = HttpContext.Current.Server.MapPath(@"~\App_Data\gamedata.sqlite");
        public static string gameDBConnectionString = "Data Source=" + gameDBFileName;

        public static SQLiteConnection GetDBConnection()
        {
            CheckGameDB();
            return new SQLiteConnection(DBManager.gameDBConnectionString);
        }

        public static void CheckGameDB()
        {
            string threadHold = "";
            if (System.IO.File.Exists(gameDBFileName))
            {

            }
            else
            {
                lock (threadHold)
                {
                    if (System.IO.File.Exists(gameDBFileName))
                    {
                        return;
                    }
                    else
                    {
                        SQLiteConnection.CreateFile(gameDBFileName);
                        using (SQLiteConnection c = new SQLiteConnection(gameDBConnectionString))
                        {

                            SQLiteCommand command = new SQLiteCommand(@"CREATE TABLE IF NOT EXISTS [Scores](
                                                                    [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                                                    [Name] NVARCHAR(2048) NOT NULL,
                                                                    [Score] INTEGER NOT NULL,
                                                                    [When] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT Null
                                                                    );", c);
                            c.Open();
                            command.ExecuteNonQuery();

                            command = new SQLiteCommand(@"CREATE TABLE IF NOT EXISTS [Feedback](
                                                                    [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                                                    [Feedback] TEXT NOT NULL,
                                                                    [When] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT Null
                                                                    );", c);
                            command.ExecuteNonQuery();

                            command = new SQLiteCommand(@"CREATE TABLE IF NOT EXISTS [BugTracker](
                                                                    [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                                                    [Bug] TEXT NOT NULL,
                                                                    [When] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT Null
                                                                    );", c);
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        public static void SubmitBug(string bug)
        {
            using (SQLiteConnection c = DBManager.GetDBConnection())
            {
                SQLiteCommand command = new SQLiteCommand("INSERT INTO [BugTracker] (Bug) VALUES (@Bug)", c);
                command.Parameters.AddWithValue("Bug", bug);

                c.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}